package com.yulian.repository;


import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;

import com.healthmarketscience.sqlbuilder.BinaryCondition;
import com.healthmarketscience.sqlbuilder.Conditions;
import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.util.CommonUtils;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.SqlConnection;
import io.vertx.sqlclient.Transaction;
import io.vertx.sqlclient.Tuple;

/***
 * 提供一些公共的方法
 * @author
 *
 */
public class CommonRepositoryImpl {

    final InternalLogger logger = Log4JLoggerFactory.getInstance(CommonRepositoryImpl.class);
    static final InternalLogger logger2 = Log4JLoggerFactory.getInstance(CommonRepositoryImpl.class);
    // 大小写ASCII码固定差距
    static int gap = 'a' - 'A';

    /**
     * 获取数据库连接
     *
     * @param client 连接池对象
     * @return 数据库连接对象
     */
    public Future<SqlConnection> getCon(PgPool client) {
        Promise<SqlConnection> promise = Promise.promise();
        client.getConnection(ar1 -> {
            if (ar1.succeeded()) {
                //Obtain our connection
                SqlConnection conn = ar1.result();
                promise.complete(conn);
            } else {
                logger.info("获取数据库连接失败: {}", ar1.cause().getMessage());
                //这里继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar1.cause());
            }
        });
        return promise.future();
    }


    /**
     * 执行不需要的 sql语句，返回数据集
     *
     * @param conn
     * @param sql
     * @return
     */
    public Future<RowSet<Row>> commonSelectRowsetBySql(SqlConnection conn, String sql) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn.preparedQuery(sql)
        .execute(ar2 -> {
            //关闭连接
            conn.close();
            if (ar2.succeeded()) {
                logger.info("commonSelectRowsetBySql, 查询结果: {}", ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("commonSelectRowsetBySql, 失败: {}", ar2.cause().getMessage());
                //这里继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 记录web端访问系统的日志，需要记录操作类型
     *
     * @param conn
     * @param user_id
     * @param operation_type 操作类型
     * @param request_url
     * @param bodyJSObj
     * @param res_data
     * @param ip
     * @return
     */
    public Future<RowSet<Row>> writePlatformLog(SqlConnection conn, int user_id, String operation_type, String request_url, JsonObject bodyJSObj, JsonObject res_data, String ip) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "INSERT INTO  sys_log(user_id, operation_type, request_url, request_data, response_data, ip)  VALUES ($1, $2, $3, $4, $5, $6)";
        conn.preparedQuery(sql)
        .execute(Tuple.of(user_id, operation_type, request_url, bodyJSObj, res_data, ip), ar2 -> {
            if (ar2.succeeded()) {
                conn.close();
                promise.complete(ar2.result());
            } else {
                logger.info("writePlatformLog, 失败: {}", ar2.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 记录web端访问系统的日志，需要记录操作类型,返回JsonObject
     *
     * @param conn
     * @param user_id
     * @param operation_type 操作类型
     * @param request_url
     * @param bodyJSObj
     * @param res_data
     * @param ip
     * @return Future<JsonObject>
     */
    public Future<JsonObject> writePlatformLogJsonObj(SqlConnection conn, int user_id, String operation_type, String request_url, JsonObject bodyJSObj, JsonObject res_data, String ip) {
        Promise<JsonObject> promise = Promise.promise();
        String sql = "INSERT INTO  sys_log(user_id, operation_type, request_url, request_data, response_data, ip)  VALUES ($1, $2, $3, $4, $5, $6) RETURNING id";
        JsonObject responseJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(Tuple.of(user_id, operation_type, request_url, bodyJSObj, res_data, ip), re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, request_url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", request_url, re.result().rowCount());
                if (re.result().rowCount() > 0) {
                    // 取出更新记录的id
                    long[] temp_id = new long[1];
                    re.result().forEach(item -> {
                        temp_id[0] = item.getLong("id");
                    });
                    responseJSObj.put("code", 200).put("msg", operation_type + "成功,记录id: " + temp_id[0]);
                } else { // 更新数据返回结果行数为0
                    responseJSObj.put("code", -1).put("msg", "没有" + operation_type);
                }
                promise.complete(responseJSObj);
            }
        });
        return promise.future();
    }

    /**
     * 过渡使用
     * 重载2
     * 记录web端访问系统的日志，没有记录操作类型
     * writePlatformLog
     *
     * @param conn
     * @param user_id
     * @param request_url
     * @param bodyJSObj
     * @param res_data
     * @param ip
     * @return
     */
    public Future<RowSet<Row>> writePlatformLog(SqlConnection conn, int user_id, String request_url, JsonObject bodyJSObj, JsonObject res_data, String ip) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "INSERT INTO  sys_log(user_id,request_url, request_data, response_data, ip)  VALUES ($1, $2, $3, $4, $5)";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(user_id, request_url, bodyJSObj, res_data, ip), ar2 -> {
            if (ar2.succeeded()) {
                //logger.info("writePlatformLog, operation success: {} ", ar2.result().rowCount());
                conn.close();
                promise.complete(ar2.result());
            } else {
                logger.info("writePlatformLog, 失败: {}", ar2.cause().getMessage());
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 记录外部接口上行(别的系统请求该系统)报文日志
     *
     * @param conn
     * @param app_name
     * @param url
     * @param req_data
     * @param res_data
     * @param is_success
     * @param req_system
     * @param res_system
     * @return
     */
    public Future<RowSet<Row>> writeUpstreamApiLog(SqlConnection conn, String app_name, String url, JsonObject req_data, JsonObject res_data, int is_success, String req_system, String res_system) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "insert into api_up_log(description, url, request_data, response_data, is_success, request_system, response_system) "
        + " VALUES ($1, $2, $3, $4, $5, $6, $7)";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(app_name, url, req_data, res_data, is_success, req_system, res_system), ar2 -> {
            conn.close();
            if (ar2.succeeded()) {
                //logger.info("writeUpstreamApiLog, operation success: "+ ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                // logger.info("writeUpstreamApiLog, operation Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 记录外部接口上行(别的系统请求该系统)报文日志,没有返回值
     *
     * @param con
     * @param app_name
     * @param url
     * @param req_data
     * @param res_data
     * @param is_success
     * @param req_system
     * @param res_system
     */
    public void writeUpstreamApiLogWithoutReturn(SqlConnection con, String app_name, String url, JsonObject req_data, JsonObject res_data, int is_success, String req_system, String res_system) {
        String sql = "insert into api_up_log(description, url, request_data, response_data, is_success, request_system, response_system) "
        + " VALUES ($1, $2, $3, $4, $5, $6, $7)";
        //Get a connection from the pool
        this.writeUpstreamApiLogBySql(con, sql, app_name, url, req_data, res_data, is_success, req_system, res_system)
        .onSuccess(rows -> {
            logger.info("writeUpstreamApiLogWithoutReturn; operaton result: " + rows.rowCount());
        })
        .onFailure(throwable -> {
            logger.info("writeUpstreamApiLogWithoutReturn; operaton result error:  " + throwable.toString());
        });
    }

    //重载1
    public Future<RowSet<Row>> writeUpstreamApiLogBySql(SqlConnection conn, String sql, String app_name, String url, JsonObject req_data, JsonObject res_data, int is_success, String req_system, String res_system) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(app_name, url, req_data, res_data, is_success, req_system, res_system), ar2 -> {
            conn.close();
            if (ar2.succeeded()) {
//		          logger.info("writePlatformLogBySql, operation success: "+ ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("writeUpstreamApiLogBySql, operation Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 记录外部接口下行(我们请求别的系统)报文日志
     *
     * @param conn
     * @param app_name
     * @param url
     * @param req_data
     * @param res_data
     * @param is_success
     * @param req_system
     * @param res_system
     * @return
     */
    public Future<RowSet<Row>> writeDownstreamApiLog(SqlConnection conn, String app_name, String url, JsonObject req_data, JsonObject res_data, int is_success, String req_system, String res_system) {
        Promise<RowSet<Row>> promise = Promise.promise();
        String sql = "insert into api_down_log(description, url, request_data, response_data, is_success, request_system, response_system) "
        + " VALUES ($1, $2, $3, $4, $5, $6, $7)";
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(app_name, url, req_data, res_data, is_success, req_system, res_system), ar2 -> {
            conn.close();
            if (ar2.succeeded()) {
                //logger.info("writeDownstreamApiLog, operation success: "+ ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                // logger.info("writeDownstreamApiLog, operation Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 记录外部接口下行(我们请求别的系统)报文日志 没有返回值
     *
     * @param con
     * @param app_name
     * @param url
     * @param req_data
     * @param res_data
     * @param is_success
     * @param request_system
     * @param res_system
     */
    public void writeDownstreamApiLogWithoutReturn(SqlConnection con, String app_name, String url, JsonObject req_data, JsonObject res_data, int is_success, String request_system, String res_system) {
        String sql = "insert into api_down_log(description, url, request_data, response_data, is_success, request_system, response_system) "
        + " VALUES ($1, $2, $3, $4, $5, $6, $7)";
        //Get a connection from the pool
        this.writeDownstreamApiLogBySql(con, sql, app_name, url, req_data, res_data, is_success, request_system, res_system)
        .onSuccess(rows -> {
            logger.info("writeDownstreamApiLogWithoutReturn; operaton result: " + rows.rowCount());
        })
        .onFailure(throwable -> {
            logger.info("writeDownstreamApiLogWithoutReturn; operaton result error:  " + throwable.toString());
        });
    }

    /**
     * 记录外部接口下行(我们请求别的系统)报文日志 重载2
     *
     * @param conn
     * @param sql
     * @param app_name
     * @param url
     * @param req_data
     * @param res_data
     * @param is_success
     * @param request_system
     * @param res_system
     * @return
     */
    public Future<RowSet<Row>> writeDownstreamApiLogBySql(SqlConnection conn, String sql, String app_name, String url, JsonObject req_data, JsonObject res_data, int is_success, String request_system, String res_system) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn
        .preparedQuery(sql)
        .execute(Tuple.of(app_name, url, req_data, res_data, is_success, request_system, res_system), ar2 -> {
            conn.close();
            if (ar2.succeeded()) {
//	          logger.info("writeDownstreamApiLogBySql, operation success: "+ ar2.result().rowCount());
                promise.complete(ar2.result());
            } else {
                logger.info("writeDownstreamApiLogBySql, operation Failed");
                //这里就相当于继续向上抛出异常，用Promise来向上抛异常
                promise.fail(ar2.cause());
            }
        });
        return promise.future();
    }

    /**
     * 验证某个参数是否必填
     *
     * @param request_data 请求的数据
     * @param param        本验证的字段
     * @return
     */
    public JsonObject verifyOneParamMust(JsonObject request_data, String param) {
        JsonObject resp = new JsonObject();
        resp.put("code", -1);
        if (request_data.getValue(param) == null) {
            resp.put("msg", "请求参数" + param + "缺失!");
        } else if (request_data.getValue(param).equals("")) {
            resp.put("msg", "请求参数" + param + "不能为空!");
        } else {
            resp = null;
        }
        return resp;
    }

    /**
     * 验证参数是否必填
     *
     * @param request_data 请求的数据
     * @param fields       ['a','b']
     * @return
     */
    public JsonObject verifyAllParamMust(JsonObject request_data, List<String> fields) {
        JsonObject resp = new JsonObject();
        resp = null;
        for (int i = 0; i < fields.size(); i++) {
            resp = verifyOneParamMust(request_data, fields.get(i));
            if (resp != null)
                break;
        }
        return resp;
    }

    /**
     * 验证某个参数是否为数值
     *
     * @param request_data 请求的数据
     * @param param        本验证的字段
     * @return
     */
    public JsonObject verifyOneParamNumeric(JsonObject request_data, String param) {
        JsonObject resp = new JsonObject();
        resp.put("code", -1);
        if (request_data.getValue(param) == null) {
            resp.put("msg", "请求参数" + param + "缺失!");
        } else if (request_data.getValue(param).equals("")) {
            resp.put("msg", "请求参数" + param + "不能为空!");
        } else if (!isNumericA(request_data.getValue(param).toString())) {
            resp.put("msg", "请求参数" + param + "不是数值!");
        } else {
            resp = null;
        }
        return resp;
    }

    /**
     * 废弃，用正则表达式判断字符串是否为整数或者小数，效率比较低
     *
     * @param str
     * @return
     */
    public boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("[0-9]*\\.?[0-9]+");
        return pattern.matcher(str).matches();
    }

    /**
     * 用ASCII判断字符串是否为整数或者小数，效率最高
     *
     * @param str
     * @return
     */
    public boolean isNumericA(String str) {
        StringBuilder stringBuilder = deleteChar(str);
        for (int i = stringBuilder.length(); --i >= 0; ) {
            int chr = stringBuilder.charAt(i);
            if (chr < 48 || chr > 57)
                return false;
        }
        return true;
    }

    public StringBuilder deleteChar(String str) {
        int flag;
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(str);
//		if (stringBuilder.charAt(0) == '+' || stringBuilder.charAt(0) == '-') {
//			stringBuilder.deleteCharAt(0);
//		}
        if ((flag = stringBuilder.indexOf(".")) != -1) {
            stringBuilder.deleteCharAt(flag);
        }
        return stringBuilder;
    }

    /**
     * 验证传入参数集是否为数值
     *
     * @param request_data 请求的数据
     * @param fields       ['a','b']
     * @return
     */
    public JsonObject verifyAllParamNumeric(JsonObject request_data, List<String> fields) {
        JsonObject resp = new JsonObject();
        resp = null;
        for (int i = 0; i < fields.size(); i++) {
            resp = verifyOneParamNumeric(request_data, fields.get(i));
            if (resp != null)
                break;
        }
        return resp;
    }

    /**
     * 验证某个字段名是否必传，值可以为空
     *
     * @param request_data 请求的数据
     * @param param        本验证的字段
     * @return
     */
    public JsonObject verifyOneParamNameMust(JsonObject request_data, String param) {
        JsonObject resp = new JsonObject();
        resp.put("code", -1);
        if (request_data.getValue(param) == null) {
            resp.put("msg", "请求参数" + param + "缺失!");
        } else {
            resp = null;
        }
        return resp;
    }

    /**
     * 验证字段名是否必传，值可以为空
     *
     * @param request_data 请求的数据
     * @param fields       ['a','b']
     * @return
     */
    public JsonObject verifyAllParamNameMust(JsonObject request_data, List<String> fields) {
        JsonObject resp = new JsonObject();
        resp = null;
        for (int i = 0; i < fields.size(); i++) {
            resp = verifyOneParamNameMust(request_data, fields.get(i));
            if (resp != null)
                break;
        }
        return resp;
    }


    /**
     * 废弃
     * 待和实例方法进行比较后再使用
     *
     * @param conn
     * @param sql
     * @param url
     * @return
     */
    public static Future<JsonArray> queryJsonArrayStatic(SqlConnection conn, String sql, String url) {
        Promise<JsonArray> promise = Promise.promise();
        JsonArray result_Array = new JsonArray();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    promise.complete(result_Array);
                    logger2.info("{} 没有满足条件的数据", url);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnsName : columnsNames) {
                            jsonObject.put(columnsName, row.getValue(columnsName));
                        }
                        result_Array.add(jsonObject);
                    }
                    promise.complete(result_Array);
                }
            } else {
                logger2.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到满足条件的记录总数
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<Integer> getRecordsCount(SqlConnection conn, String sql, String url) {
        Promise<Integer> promise = Promise.promise();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(0);
                } else {
                    Integer[] total_records = new Integer[1];
                    re.result().forEach(item -> {
                        total_records[0] = item.getInteger("total");
                    });
                    promise.complete(total_records[0]);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到满足条件的记录总数, 带Tuple参数
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<Integer> getRecordsCount(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<Integer> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(0);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    Integer[] total_records = new Integer[1];
                    re.result().forEach(item -> {
                        total_records[0] = Integer.valueOf(item.getValue(columnsNames.get(0))==null? "0":item.getValue(columnsNames.get(0)).toString());
                    });
                    promise.complete(total_records[0]);

                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到满足条件的记录总数, sql语句中含有字段 total，形如count(*) as total
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<Integer> getRecordsCount(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<Integer> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据,{}", url, msg);
                    promise.complete(0);
                } else {
                    Integer[] total_records = new Integer[1];
                    re.result().forEach(item -> {
                        total_records[0] = item.getInteger("total");
                    });
                    promise.complete(total_records[0]);

                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, msg));
            }
        });
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件的记录数的sql语句
     *
     * @param queryJS    参数对象
     * @param table_name 数据表名
     * @return
     */
    public String createCountSql(JsonObject queryJS, String table_name) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql(" count(*) as total ")).addCustomFromTable(table_name);
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createCountSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate().toString();
    }

    /**
     * 创建查询符合搜索条件的记录数的sql语句, where子句为模糊匹配
     *
     * @param queryJS    参数对象
     * @param table_name 数据表名
     * @return
     */
    public String createFuzzyCountSql(JsonObject queryJS, String table_name) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql(" count(*) as total ")).addCustomFromTable(table_name);
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    //支持模糊查询
                    sq.getWhereClause().addCustomCondition(BinaryCondition.like(new CustomSql(key), "%" + value + "%"));
                }
            } catch (Exception e) {
                logger.info("createFuzzyCountSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate().toString();
    }

    /**
     * 查询数据表得到编码和名称对的Json对象
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonObject> getCodeNamePairJsonObject(SqlConnection conn, String sql, String url) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject jsObj = new JsonObject();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(jsObj);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    re.result().forEach(item -> {
                        jsObj.put(item.getValue(columnsNames.get(0)).toString(), item.getValue(columnsNames.get(1)).toString());
                    });
                    promise.complete(jsObj);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到编码和名称对的Json对象
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonObject> getIdNamePairJsonObject(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject jsObj = new JsonObject();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(jsObj);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    re.result().forEach(item -> {
                        jsObj.put(item.getValue(columnsNames.get(0)).toString(), item.getValue(columnsNames.get(1)).toString());
                    });
                    promise.complete(jsObj);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询一条记录,如果不存在记录,则向上抛出异常错误; 如果存在，取出编码对应的名称
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return RowSet<Row>
     */
    public Future<JsonObject> queryOneJsonObjectThrow(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            } else {
                if (null == re.result() || re.result().rowCount() <= 0) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(500, msg));
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    JsonObject jsonObject = new JsonObject();
                    for (Row row : re.result()) {
                        for (String columnsName : columnsNames) {
                            jsonObject.put(columnsName, row.getValue(columnsName));
                        }
                    }
                    promise.complete(jsonObject);
                }
            }
        });
        return promise.future();
    }

    /**
     * 用于***Manager类在新增或更新数据时，需要根据一些条件从其它表获取数据，当数据不存在时，返回一个空的json对象。
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return RowSet<Row>
     */
    public Future<JsonObject> queryOneJsonObject(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            } else {
                if (null == re.result() || re.result().rowCount() <= 0) {
                    JsonObject jsonObject = new JsonObject();
                    promise.complete(jsonObject);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    JsonObject jsonObject = new JsonObject();
                    for (Row row : re.result()) {
                        for (String columnsName : columnsNames) {
                            jsonObject.put(columnsName, row.getValue(columnsName));
                        }
                    }
                    promise.complete(jsonObject);
                }
            }
        });
        return promise.future();
    }

    /**
     * 查询多条记录,并判断所得记录数是否和传入记录数相等
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return RowSet<Row>
     */
    public Future<JsonObject> recordsCountMatchThrow(SqlConnection conn, String sql, Tuple parameter, int count, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject jsObj = new JsonObject();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, msg + " 服务器处理异常"));
            } else {
                //logger.info(re.result().rowCount());
                if (null == re.result() || re.result().rowCount() != count) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(500, msg));
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    re.result().forEach(item -> {
                        jsObj.put(item.getValue(columnsNames.get(0)).toString(), item.getValue(columnsNames.get(1)).toString());
                    });
                    promise.complete(jsObj);
                }
            }
        });
        return promise.future();
    }

    /**
     * 满足条件的记录数,可以和getRecordsCount交互使用
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return RowSet<Row>
     */
    public Future<Integer> getRecordCountFuture(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<Integer> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(500, msg));
                } else {
                    int record_count = 0;
                    if (re.result().rowCount() > 0)
                        record_count = re.result().rowCount();
                    promise.complete(record_count);
                }
            }
        });
        return promise.future();
    }

    /**
     * 取最新记录编码code
     *
     * @param conn
     * @return
     */
    public Future<String> getCodeFuture(SqlConnection conn, String sql, String url, String msg) {
        Promise<String> promise = Promise.promise();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(500, msg));
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    String[] temp_code = new String[1];
                    if (re.result().rowCount() > 0) {
                        re.result().forEach(item -> {
                            temp_code[0] = item.getString(columnsNames.get(0));
                        });
                        promise.complete(temp_code[0]);
                    } else {
                        promise.complete("");
                    }
                }
            }
        });
        return promise.future();
    }

    /**
     * 获取满足条件的记录的编码 code
     *
     * @param conn
     * @param sql
     * @param parameter
     * @param url
     * @param msg
     * @return
     */
    public Future<String> getCodeFuture(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<String> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(500, msg));
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    String[] temp_code = new String[1];
                    if (re.result().rowCount() > 0) {
                        re.result().forEach(item -> {
                            temp_code[0] = item.getString(columnsNames.get(0));
                        });
                        promise.complete(temp_code[0]);
                    } else {
                        promise.complete("");
                    }
                }
            }
        });
        return promise.future();
    }

    /**
     * 获取满足条件的记录的编码 code
     *
     * @param conn
     * @param sql
     * @param parameter
     * @param url
     * @param msg
     * @return
     */
    public Future<String> getCodeFuture2(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<String> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(500, msg));
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    String[] a = new String[1];
                    if (re.result().rowCount() > 0) {
                        re.result().forEach(item -> {
                            logger.info("item:{}",item.toJson());
                            a[0] = item.getValue(columnsNames.get(0))==null? "0":item.getValue(columnsNames.get(0)).toString();
                        });
                        promise.complete(String.valueOf(a[0]));
                    } else {
                        promise.complete("");
                    }
                }
            }
        });
        return promise.future();
    }

    /**
     * 通过名字获取编码，用于批量导入数据时，校验传入的名字的合法性，如果合法，返回对应的编码，否则返回某行某列数据不合法
     *
     * @param conn
     * @param sql
     * @param name
     * @param noticeName
     * @param row_num
     * @param url
     * @return
     */
    public Future<JsonObject> getCodeByNameFuture(SqlConnection conn, String sql, String name, String noticeName, int row_num, String url) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(Tuple.of(name), re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "数据库异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(800, "数据库异常"));
                } else {
                    String[] temp_code = new String[1];
                    if (re.result().rowCount() > 0) {
                        re.result().forEach(item -> {
                            //获取第一个字段的值
                            temp_code[0] = item.getString(0);
                        });
                        resultJSObj.put("code", 0).put("data", temp_code[0]);
                    } else {
                        resultJSObj.put("code", -1).put("msg", "第" + row_num + "行的" + noticeName + ": " + name + "不合法");
                    }
                    promise.complete(resultJSObj);
                }
            }
        });
        return promise.future();
    }


    /**
     * 通过名字获取Id，用于批量导入数据时，校验传入的名字的合法性，如果合法，返回对应的id，否则返回某行某列数据不合法
     *
     * @param conn
     * @param sql
     * @param name
     * @param noticeName
     * @param row_num
     * @param url
     * @return
     */
    public Future<JsonObject> getIdByNameFuture(SqlConnection conn, String sql, String name, String noticeName, int row_num, String url) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(Tuple.of(name), re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "数据库异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(800, "数据库异常"));
                } else {
                    String[] temp_code = new String[1];
                    if (re.result().rowCount() > 0) {
                        re.result().forEach(item -> {
                            //获取第一个字段的值
                            temp_code[0] = item.getString(0);
                        });
                        resultJSObj.put("id", 0).put("data", temp_code[0]);
                    } else {
                        resultJSObj.put("id", -1).put("msg", "第" + row_num + "行的" + noticeName + ": " + name + "不合法");
                    }
                    promise.complete(resultJSObj);
                }
            }
        });
        return promise.future();
    }
    /**
     * 通过名字获取Id，用于批量导入数据时，校验传入的名字的合法性，如果合法，返回对应的id，否则返回某行某列数据不合法
     *
     * @param conn
     * @param sql
     * @param name
     * @param noticeName
     * @param row_num
     * @param url
     * @return
     */
    public Future<JsonObject> getIdByNameFuture2(SqlConnection conn, String sql, String name, String noticeName, int row_num, String url) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(Tuple.of(name), re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "数据库异常"));
            } else {
                if (null == re.result()) {
                    promise.fail(CommonUtils.createExceptionJsonObjectStr(800, "数据库异常"));
                } else {
                    String[] temp_code = new String[1];
                    if (re.result().rowCount() > 0) {
                        re.result().forEach(item -> {
                            //获取第一个字段的值
                            temp_code[0] = String.valueOf(item.getLong(0));
                        });
                        resultJSObj.put("id", 0).put("data", temp_code[0]);
                    } else {
                        resultJSObj.put("id", -1).put("msg", "第" + row_num + "行的" + noticeName + ": " + name + "不合法");
                    }
                    promise.complete(resultJSObj);
                }
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到Json数组对象JsonArray，
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonArray> queryJsonArray(SqlConnection conn, String sql, String url) {
        Promise<JsonArray> promise = Promise.promise();
        JsonArray result_Array = new JsonArray();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(result_Array);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            if (columnName.contains("_time") || columnName.contains("_date") || columnName.contains("time_line"))
                                jsonObject.put(columnName, row.getValue(columnName).toString());
                            else
                                jsonObject.put(columnName, row.getValue(columnName));
                        }
                        result_Array.add(jsonObject);
                    }
                    promise.complete(result_Array);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到Json数组对象JsonArray，含参数元组
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonArray> queryJsonArray(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<JsonArray> promise = Promise.promise();
        JsonArray result_Array = new JsonArray();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(result_Array);

                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            if (columnName.contains("_time") || columnName.contains("_date"))
                                jsonObject.put(columnName, row.getValue(columnName).toString());
                            else
                                jsonObject.put(columnName, row.getValue(columnName));
                        }
                        result_Array.add(jsonObject);
                    }
                    promise.complete(result_Array);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 查询数据表得到Json数组对象JsonArray， 字段的值转为字符串，给前端筛选下拉框赋值用
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonArray> queryJsonArrayString(SqlConnection conn, String sql, String url) {
        Promise<JsonArray> promise = Promise.promise();
        JsonArray result_Array = new JsonArray();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(result_Array);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            jsonObject.put(columnName, row.getValue(columnName).toString());
                        }
                        result_Array.add(jsonObject);
                    }
                    promise.complete(result_Array);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 含参数元组,查询数据表得到Json数组对象JsonArray，字段的值转为字符串，给前端筛选下拉框赋值用
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonArray> queryJsonArrayString(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<JsonArray> promise = Promise.promise();
        JsonArray result_Array = new JsonArray();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(result_Array);

                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            jsonObject.put(columnName, row.getValue(columnName).toString());
                        }
                        result_Array.add(jsonObject);
                    }
                    promise.complete(result_Array);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }


    /**
     * 查询数据表得到Json数组对象JsonArray，数据表字段名由蛇形转为驼峰
     *
     * @param conn 连接
     * @param sql
     * @param url
     * @return
     */
    public Future<JsonArray> queryJsonArrayCamel(SqlConnection conn, String sql, String url) {
        Promise<JsonArray> promise = Promise.promise();
        JsonArray result_Array = new JsonArray();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(result_Array);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            String camelColumnName = parseStrToCamel(columnName);
                            jsonObject.put(camelColumnName, row.getValue(columnName).toString());
                        }
                        result_Array.add(jsonObject);
                    }
                    promise.complete(result_Array);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 将蛇形字符串转为驼峰字符串
     *
     * @param str
     * @return
     */
    private String parseStrToCamel(String str) {
        StringBuilder sb = new StringBuilder();
        boolean needToUpperCase = false;
        for (int i = 0; i < str.length(); i++) {
            char ch = str.charAt(i);
            if (needToUpperCase) {
                if ('a' < ch && ch < 'z') {
                    char temp = (char) (ch - gap);
                    sb.append(temp);
                }
                needToUpperCase = false;
                continue;
            }
            if (ch == '_') {
                needToUpperCase = true;
            } else {
                sb.append(ch);
            }
        }
        return sb.toString();
    }


    /**
     * 查询返回RowSet<Row>,带参数
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return Future<RowSet < Row>>
     */
    public Future<RowSet<Row>> queryRowSet(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                promise.complete(re.result());
            }
        });
        return promise.future();
    }

    /**
     * 查询返回RowSet<Row>
     *
     * @param conn      连接
     * @param sql       sql
     * @return Future<RowSet < Row>>
     */
    public Future<RowSet<Row>> queryRowSet(SqlConnection conn, String sql, String url) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                promise.complete(re.result());
            }
        });
        return promise.future();
    }

    /**
     * 保存或者修改，返回JsonObject
     *
     * @param conn      连接
     * @param sql       sql语句
     * @param parameter 参数
     * @param url       路径
     * @param msg       提示信息
     * @return
     */
    public Future<JsonObject> insertOrUpdate(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                if (re.result().rowCount() > 0) {
                    // 取出更新记录的id
                    long[] temp_id = new long[1];
                    re.result().forEach(item -> {
                        temp_id[0] = item.getLong("id");
                    });
                    responseJSObj.put("code", 200).put("msg", msg + "成功,记录id: " + temp_id[0]);
                } else { // 更新数据返回结果行数为0
                    responseJSObj.put("code", -1).put("msg", "没有" + msg);
                }
                promise.complete(responseJSObj);
            }
        });
        return promise.future();
    }

    /**
     * 保存或者修改，返回JsonObject
     *
     * @param conn      连接
     * @param sql       sql语句
     * @param parameter 参数
     * @param url       路径
     * @param msg       提示信息
     * @return
     */
    public Future<JsonObject> insertOrUpdateBatch(SqlConnection conn, String sql, List<Tuple> parameter, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseJSObj = new JsonObject();
        conn.preparedQuery(sql).executeBatch(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                if (re.result().rowCount() > 0) {
                    // 取出更新记录的id
                    long[] temp_id = new long[re.result().rowCount()];
                    int[] count = new int[1];
                    count[0] = 0;
                    for (RowSet<Row> rows = re.result(); rows.next() != null; rows = rows.next()) {
                        temp_id[count[0]] = rows.iterator().next().getInteger("id");
                        count[0] = count[0] + 1;
                    }
                    responseJSObj.put("code", 200).put("msg", msg + "成功,记录id: " + Arrays.toString(temp_id));
                } else { // 更新数据返回结果行数为0
                    conn.close();
                    responseJSObj.put("code", -1).put("msg", "没有" + msg);
                }
                promise.complete(responseJSObj);
            }
        });
        return promise.future();
    }

    /**
     * 调用DB函数，返回JsonObject
     *
     * @param conn      连接
     * @param sql       sql语句
     * @param parameter 参数
     * @param url       路径
     * @param msg       提示信息
     * @return
     */
    public Future<JsonObject> callDBFunction(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{},运行DB函数返回: {} ", url, re.result().rowCount());
                if (re.result().rowCount() > 0) {
                    int[] result = new int[1];
                    re.result().forEach(item -> {
                        result[0] = item.getInteger(0);
                    });
                    if (result[0] == 10) { //表示DB函数运行成功
                        responseJSObj.put("code", 200).put("msg", msg + "成功");
                    } else {
                        responseJSObj.put("code", -1).put("msg", msg + "失败");
                    }
                } else {  //DB函数运行没有返回结果
                    responseJSObj.put("code", -1).put("msg", msg + "失败");
                }
                promise.complete(responseJSObj);
            }
        });
        return promise.future();
    }

    /**
     * 准入或生效，返回JsonObject
     *
     * @param conn      连接
     * @param sql       sql语句
     * @param parameter 参数
     * @param url       路径
     * @param msg       提示信息
     * @return
     */
    public Future<JsonObject> admitOrDelete(SqlConnection conn, String sql, Tuple parameter, String url, String msg) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseJSObj = new JsonObject();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                if (re.result().rowCount() > 0) {
                    responseJSObj.put("code", 200).put("msg", msg + "成功");
                } else { // 更新数据返回结果行数为0
                    conn.close();
                    responseJSObj.put("code", -1).put("msg", "没有" + msg);
                }
                promise.complete(responseJSObj);
            }
        });
        return promise.future();
    }

    /**
     * 保存或者修改，返回RowSet<Row>
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return Future<RowSet < Row>>
     */
    public Future<RowSet<Row>> insertOrUpdate(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                promise.complete(re.result());
            }
        });
        return promise.future();
    }

    /**
     * 没有参数的保存或者修改，RowSet<Row>
     *
     * @param conn      连接
     * @param sql       sql
     * @return Future<RowSet < Row>>
     */
    public Future<RowSet<Row>> insertOrUpdate(SqlConnection conn, String sql, String url) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                promise.complete(re.result());
            }
        });
        return promise.future();
    }

    /**
     * 批量保存或者修改
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return Future<RowSet < Row>>
     */
    public Future<RowSet<Row>> insertOrUpdateBatch(SqlConnection conn, String sql, List<Tuple> parameter, String url) {
        Promise<RowSet<Row>> promise = Promise.promise();
        conn.preparedQuery(sql).executeBatch(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                logger.info("{}, 插入或更新记录数: {} ", url, re.result().rowCount());
                promise.complete(re.result());
            }
        });
        return promise.future();
    }

    /**
     * 将列表 [1000, 10001]处理后得到字符串 1000,10001  或者 将列表 [无锡地区, 常州地区] 处理后得到字符串 无锡地区,常州地区
     *
     * @param strList
     * @return
     */
    public String getStrList(String strList) {
        String final_str = "";
        if (!strList.equals("") && !strList.equals("[]")) {
            String firstChar = strList.substring(0, 1);
            if (firstChar.equals("["))
                final_str = strList.substring(1, strList.length() - 1);
            final_str = final_str.replace(", ", ",");
        }
        return final_str;
    }

    /**
     * 删除
     *
     * @param conn  连接
     * @param sql   sql
     * @param tuple 参数
     */
    public Future<JsonObject> del(SqlConnection conn, String sql, Tuple tuple) {
        Promise<JsonObject> promise = Promise.promise();
        conn.preparedQuery(sql).execute(tuple, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, "del", "操作失败", sql));
            } else {
                promise.complete(CommonUtils.createJsonObject(null));
            }
        });
        return promise.future();
    }


    /**
     * 事务保存
     *
     * @param conn     连接
     * @param promise  promise
     * @param sql      sql
     * @param tuple    参数
     * @param url      url
     * @param complete 返回
     * @param tx       事务
     */
    public void transactionInsertOrUpdate(SqlConnection conn, Promise<JsonObject> promise, String sql, List<Tuple> tuple, String url, Boolean complete, Transaction tx) {
        conn.preparedQuery(sql).executeBatch(tuple, re -> {
            if (re.failed()) {
                logger.info(re.cause().getMessage());
                conn.close();
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                if (re.result().rowCount() > 0) {
                    // 取出更新记录的id
                    long[] temp_id = new long[re.result().rowCount()];
                    int[] count = new int[1];
                    count[0] = 0;
                    for (RowSet<Row> rows = re.result(); rows.next() != null; rows = rows.next()) {
                        temp_id[count[0]] = rows.iterator().next().getInteger("id");
                        count[0] = count[0] + 1;
                    }
                    logger.info("成功,记录id: " + Arrays.toString(temp_id));
                }
                if (null != tx) {
                    tx.commit();
                }
                if (Boolean.TRUE.equals(complete)) {
                    conn.close();
                    promise.complete(CommonUtils.createJsonObject(null));
                }
            }
        });
    }

    /**
     * 事务保存
     *
     * @param conn     连接
     * @param promise  promise
     * @param sql      sql
     * @param tuple    参数
     * @param url      url
     * @param complete 返回
     * @param tx       事务
     */
    public void transactionInsertOrUpdate(SqlConnection conn, Promise<JsonObject> promise, String sql, Tuple tuple, String url, Boolean complete, Transaction tx) {
        conn.preparedQuery(sql).execute(tuple, re -> {
            if (re.failed()) {
                conn.close();
                logger.info(re.cause().getMessage());
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                if (re.result().rowCount() > 0) {
                    // 取出更新记录的id
                    long[] temp_id = new long[re.result().rowCount()];
                    int[] count = new int[1];
                    count[0] = 0;
                    for (RowSet<Row> rows = re.result(); rows.next() != null; rows = rows.next()) {
                        temp_id[count[0]] = rows.iterator().next().getInteger("id");
                        count[0] = count[0] + 1;
                    }
                    logger.info("成功,记录id: " + Arrays.toString(temp_id));
                }
                if (null != tx) {
                    tx.commit();
                }
                if (Boolean.TRUE.equals(complete)) {
                    conn.close();
                    promise.complete(CommonUtils.createJsonObject(null));
                }
            }
        });
    }

    /**
     * 事务保存
     *
     * @param conn     连接
     * @param promise  promise
     * @param sql      sql
     * @param url      url
     * @param complete 返回
     * @param tx       事务
     */
    public void transactionInsertOrUpdate(SqlConnection conn, Promise<JsonObject> promise, String sql, String url, Boolean complete, Transaction tx) {
        conn.preparedQuery(sql).execute(re -> {
            if (re.failed()) {
                conn.close();
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                if (null != tx) {
                    tx.commit();
                }
                if (Boolean.TRUE.equals(complete)) {
                    conn.close();
                    promise.complete(CommonUtils.createJsonObject(null));
                }
            }
        });
    }

    /**
     * 处理查询条件
     * 2023-09-08
     *
     * @param sq      查询符合条件的SQL语句
     * @param queryJS 查询条件
     * @author dameng
     */
    public void processQueryJsonObject(SelectQuery sq, JsonObject queryJS) {
        for (String key : queryJS.fieldNames()) {
            try {
                String value = queryJS.getString(key);
                if (key.contains("start_")) {
                    sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql(key.substring(6)), LocalDate.parse(value)));
                } else if (key.contains("end_")) {
                    sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql(key.substring(4)), LocalDate.parse(value)));
                } else if (!"[]".equals(value)) {
                    String firstChar = value.substring(0, 1);
                    if ("[".equals(firstChar)) {
                        value = value.substring(1, value.length() - 1);
                    }
                    value = value.replace(", ", "','");
                    // 添加where InCondition子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("processQueryJsonObject发生错误, 错误原因: ", e.getMessage());
            }
        }
    }

    /**
     * 此方法用于判定请求参数是否符合范围
     * 2023-09-08
     *
     * @param config   配置对象
     * @param fileName 配置文件名
     * @param key      待验证的参数
     * @return 验证的结果
     * @author dameng
     */
    public JsonObject verifyParamRange(JsonObject config, String fileName, String key) {
        JsonObject valueInfo = config.getJsonObject(fileName);
        if (!valueInfo.containsKey(key)) {
            JsonObject resp = new JsonObject();
            resp.put("code", -1).put("msg", "请求参数”" + key + "“不符合范围!");
            return resp;
        }
        return null;
    }

    /**
     * 20230908
     *
     * @param conn
     * @param sql
     * @param url
     * @return
     * @author junfeng
     */
    public Future<JsonObject> queryJsonArrayManyToMany(SqlConnection conn, String sql, String url) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJsonObject = new JsonObject();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(resultJsonObject);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            jsonObject.put(columnName, row.getValue(columnName).toString());
                        }
                        String id = jsonObject.getString("id");
                        resultJsonObject.put(id, jsonObject);
                    }
                    promise.complete(resultJsonObject);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    public Future<JsonObject> queryJsonArrayManyToMany(SqlConnection conn, String sql, String url, String key) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject resultJsonObject = new JsonObject();
        conn.preparedQuery(sql).execute(re -> {
            conn.close();
            if (re.succeeded()) {
                if (re.result().size() <= 0) {
                    logger.info("{} 没有满足条件的数据", url);
                    promise.complete(resultJsonObject);
                } else {
                    List<String> columnsNames = re.result().columnsNames();
                    for (Row row : re.result()) {
                        JsonObject jsonObject = new JsonObject();
                        for (String columnName : columnsNames) {
                            jsonObject.put(columnName, row.getValue(columnName).toString());
                        }
                        String key_word = jsonObject.getString(key);
                        resultJsonObject.put(key_word, jsonObject);
                    }
                    promise.complete(resultJsonObject);
                }
            } else {
                logger.info("{} 发生异常,原因: {}", url, re.cause().getMessage());
                promise.fail(CommonUtils.createJsonObject(re, url, "服务器处理异常"));
            }
        });
        return promise.future();
    }

    /**
     * 事务保存,返回新增ID
     *
     * @param conn  连接
     * @param sql   sql
     * @param url   url
     */
    public Future<List<Long>> transactionInsertOrUpdate(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<List<Long>> promise = Promise.promise();
        List<Long> ids = new ArrayList<>();
        conn.preparedQuery(sql).execute(parameter, re -> {
            if (re.failed()) {
                conn.close();
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                RowSet<Row> result = re.result();
                for (Row row : result) {
                    ids.add(row.getLong("id"));
                }
                promise.complete(ids);
            }
        });
        return promise.future();
    }

    /**
     * 事务保存,返回新增id list , 支持批量倒入
     *
     * @param conn  连接
     * @param sql   sql
     * @param url   url
     */
    public Future<List<Long>> transactionInsertOrUpdate(SqlConnection conn, String sql, List<Tuple> parameter, String url) {
        Promise<List<Long>> promise = Promise.promise();
        List<Long> ids = new ArrayList<>();
        conn.preparedQuery(sql).executeBatch(parameter, re -> {
            if (re.failed()) {
                conn.close();
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                RowSet<Row> result = re.result();
                for (Row row : result) {
                    ids.add(row.getLong("id"));
                }
                promise.complete(ids);
            }
        });
        return promise.future();
    }


    /**
     * 功能模块2所使用的静态方法
     */

    /**
     * 保存(不含事务控制）
     *
     * @param conn      连接
     * @param sql       sql
     * @param parameter 参数
     * @return JsonObject
     */
    public static Future<JsonObject> save(SqlConnection conn, String sql, Tuple parameter, String url) {
        Promise<JsonObject> promise = Promise.promise();
        conn.preparedQuery(sql).execute(parameter, re -> {
            conn.close();
            if (re.failed()) {
                promise.fail(CommonUtils.createExceptionJsonObjectStr(re, url, "服务器处理异常", sql));
            } else {
                promise.complete(CommonUtils.createJsonObject(null));
            }
        });
        return promise.future();
    }

    /**
     * 添加分页
     *
     * @param sql     sql
     * @param current 当前页
     * @param size    大小
     * @return sql
     */
    public static String addPageCondition(String sql, Integer current, Integer size) {
        current = (current - 1) * size;
        return sql + " limit " + size + " offset " + current;
    }

}
